package DataAccessLayer;
use strict;
use DBI;
use DBI qw(:sql_types);
use POSIX qw(strftime);
use threads::shared;
use Proc::ProcessTable; 

sub new {
    my $subclass=shift; 	
	my $self = {};
	
    if ( $subclass ) { # if not nill...
        eval " use $subclass; " ;
        die $@ if $@;
        $self= new $subclass;
        $self->{URL} = undef;				# the url connection eg. DBI:Oracle:T:Machine:SID
        $self->{USER} = undef;				# the database user
        $self->{PASSWORD} = undef;                      # the password       
        $self->{ORA_DBH_SHARE} = undef; 
      
      bless $self,$subclass;
      return $self;
    } else {
    	$self->{URL} = undef;				# the url connection eg. DBI:Oracle:T:Machine:SID
        $self->{USER} = undef;				# the database user
        $self->{PASSWORD} = undef;                      # the password        
        bless $self; 
        return $self; 
    } 
}

###########################
## set/get url  ##
###########################
sub url {
	my $self = shift;
	if (@_) {$self->{URL} = shift};
	return $self->{URL};			
}

###########################
## set/get user  ##
###########################
sub user {
	my $self = shift;
	if (@_) {$self->{USER} = shift};
	return $self->{USER};			
}

###########################
## set/get password  ##
###########################
sub password {
	my $self = shift;
	if (@_) {$self->{PASSWORD} = shift};
	return $self->{PASSWORD};
}

###########################
## set/get ORA_DBH_SHARE  ##
###########################
sub ora_dbh_share {
	my $self = shift;
	if (@_) {$self->{ORA_DBH_SHARE} = shift};
	return $self->{ORA_DBH_SHARE};			
}


sub insert_market_info {
    my $self = shift;    
    my $market_info = shift;
    my $auto_commit = shift;
    my $status = {};
    eval {
	    my $dbh = $self->{DBH};
	    if (!ref($dbh)) {
	        $self->_init_db();
	        $dbh = $self->{DBH};
	    }
	    $dbh->{AutoCommit}=$auto_commit;
	        
	    my $sql = q{
	            BEGIN
	              pkg_stock.insert_market_info (
	                :p_seq_id,
					:p_channel_id,
					:p_channel_priority,
					:p_sec_center,
					:p_market_index,
					:p_index_change,
					:p_index_change_pct,
					:p_trading_qualtity,
					:p_trading_volume,
					:p_trading_value,
					:p_status,
					:p_session_no,
					to_date(:p_trading_date,'dd/mm/yyyy HH24:MI:SS')
					);
	            END;
	            };
	    my $csr = $dbh->prepare($sql) or die "Can't prepare $sql: $dbh->errstr\n";
	    $csr->bind_param(":p_seq_id", $market_info->get('SYS_SEQ'), SQL_DOUBLE);
	    $csr->bind_param(":p_channel_id", $market_info->get('CHANNEL_NAME'), SQL_VARCHAR);
	    $csr->bind_param(":p_channel_priority", $market_info->get('PRIORITY'), SQL_INTEGER);
	    $csr->bind_param(":p_sec_center", $market_info->get('SECURITY_CENTER'), SQL_INTEGER);
	    $csr->bind_param(":p_market_index",$market_info->get('Index'), SQL_VARCHAR);
	    $csr->bind_param(":p_index_change", $market_info->get('ChangeIndex'), SQL_VARCHAR);
	    $csr->bind_param(":p_index_change_pct", $market_info->get('ChangePercent'), SQL_INTEGER);
	    $csr->bind_param(":p_trading_qualtity",$market_info->get('Transactions'), SQL_INTEGER);
	    $csr->bind_param(":p_trading_volume", $market_info->get('TotalVolume'), SQL_INTEGER);
	    $csr->bind_param(":p_trading_value", $market_info->get('TotalValue'), SQL_INTEGER);
	    $csr->bind_param(":p_status", '', SQL_VARCHAR);
	    $csr->bind_param(":p_session_no",$market_info->get('SessionNo'), SQL_VARCHAR);
	    $csr->bind_param(":p_trading_date",$market_info->get('TradingDate')?$market_info->get('TradingDate') . " " . $market_info->get('Time'):undef, SQL_VARCHAR);        
	    $csr->execute() || die "can't execute $sql: $dbh->errstr\n";
	    $csr->finish();
	    $market_info = undef;
	};
	
    if($@){
    	$status->{CODE} = 'FAILED';
    	$status->{MSG} = "$DBI::errstr";
    }else{
    	$status->{CODE} = 'OK';
    	$status->{MSG} = "Successfully insert the market info to the database";
    }
    return $status;
}

sub insert_stock_info {
    my $self = shift;    
    my $stock_info = shift;
    my $auto_commit = shift;
    my $status = {};
    eval {
	    my $dbh = $self->{DBH};
	    if (!ref($dbh)) {
	        $self->_init_db();
	        $dbh = $self->{DBH};
	    }
	    $dbh->{AutoCommit}=$auto_commit;
	    
	    my $sql = q{
	            BEGIN
	              pkg_stock.insert_stock_info (
	                :p_seq_id,
			        :p_channel_id,
					:p_stock_symbol,
					:p_basic_price,
					:p_ceiling_price,
					:p_floor_price,
					:p_offer_price1,
					:p_offer_volume1,
					:p_bid_price1,
					:p_bid_volume1,
					:p_offer_price2,
					:p_offer_volume2,
					:p_bid_price2,
					:p_bid_volume2,
					:p_offer_price3,
					:p_offer_volume3,
					:p_bid_price3,
					:p_bid_volume3,
					:p_match_price,
					:p_match_volume,
					:p_match_change,
					:p_open_price,
					:p_close_price,
					:p_highest_price,
					:p_lowest_price,
			        :p_description);
	            END;
	            };
	    my $csr = $dbh->prepare($sql) or die "can't prepare $sql: $dbh->errstr\n";
	    
	    $csr->bind_param(":p_seq_id", $stock_info->get('SYS_SEQ'), SQL_DOUBLE);
	    $csr->bind_param(":p_channel_id", $stock_info->get('CHANNEL_NAME'), SQL_VARCHAR);
	    $csr->bind_param(":p_stock_symbol", $stock_info->get('Symbol'), SQL_VARCHAR);
	    $csr->bind_param(":p_basic_price", $stock_info->get('RefPrice'), SQL_DOUBLE);
	    $csr->bind_param(":p_ceiling_price",$stock_info->get('CeilingPrice'), SQL_DOUBLE);
	    $csr->bind_param(":p_floor_price", $stock_info->get('FloorPrice'), SQL_DOUBLE);
	    $csr->bind_param(":p_offer_price1", $stock_info->get('Best1OffPrice'), SQL_DOUBLE);
	    $csr->bind_param(":p_offer_volume1",$stock_info->get('Best1OffVolume'), SQL_DOUBLE);
	    $csr->bind_param(":p_bid_price1", $stock_info->get('Best1BidPrice'), SQL_DOUBLE);
	    $csr->bind_param(":p_bid_volume1", $stock_info->get('Best1BidVolume'), SQL_DOUBLE);
	    $csr->bind_param(":p_offer_price2", $stock_info->get('Best2OffPrice'), SQL_DOUBLE);
	    $csr->bind_param(":p_offer_volume2",$stock_info->get('Best2OffVolume'), SQL_DOUBLE);
	    $csr->bind_param(":p_bid_price2", $stock_info->get('Best2BidPrice'), SQL_DOUBLE);
	    $csr->bind_param(":p_bid_volume2", $stock_info->get('Best2BidVolume'), SQL_DOUBLE);
	    $csr->bind_param(":p_offer_price3", $stock_info->get('Best3OffPrice'), SQL_DOUBLE);
	    $csr->bind_param(":p_offer_volume3",$stock_info->get('Best3OffVolume'), SQL_DOUBLE);
	    $csr->bind_param(":p_bid_price3", $stock_info->get('Best3BidPrice'), SQL_DOUBLE);
	    $csr->bind_param(":p_bid_volume3", $stock_info->get('Best3BidVolume'), SQL_DOUBLE);
	    $csr->bind_param(":p_match_price", $stock_info->get('TempPrice'), SQL_DOUBLE);
	    $csr->bind_param(":p_match_volume",$stock_info->get('TempVolume'), SQL_DOUBLE);
	    $csr->bind_param(":p_match_change",$stock_info->get('ChangedPrice'), SQL_VARCHAR);
	    $csr->bind_param(":p_open_price",$stock_info->get('SS1MatchPrice'), SQL_DOUBLE);
	    $csr->bind_param(":p_close_price",$stock_info->get('ClosePrice'), SQL_DOUBLE);
	    $csr->bind_param(":p_highest_price",$stock_info->get('HighestPrice'), SQL_DOUBLE);
	    $csr->bind_param(":p_lowest_price",$stock_info->get('LowestPrice'), SQL_DOUBLE);
	    $csr->bind_param(":p_description",$stock_info->get('Description'), SQL_VARCHAR);
    	$csr->execute() || die "can't execute $sql: $dbh->errstr\n";
    	$csr->finish();
    };
    if($@){
    	$status->{CODE} = 'FAILED';
    	$status->{MSG} = "$DBI::errstr";
    }else{
    	$status->{CODE} = 'OK';
    	$status->{MSG} = "Successfully insert the market info into the database";
    }
    return $status;
}

###########################
## insert stock info set ##
###########################
sub insert_stock_info_set {
    my $start_time = time;
    my $self = shift;
    my $stock_info_set = shift;
    my $market_info = $stock_info_set->market_info();
    my $stock_infos = $stock_info_set->stock_infos();
    my $status = {};
    
    eval {
    	my $dbh = $self->{DBH};
	    if (!defined($dbh)) {
	    	$self->_init_db();
	        $dbh = $self->{DBH};
	    }
	    $dbh->{AutoCommit}=0;
	        
	    $self->_insert_sys_stats($stock_info_set);
	    if(ref($market_info)){
		   $self->insert_market_info($market_info);
	    }
	    
	    if(ref($stock_infos)){
	    	$self->_insert_stock_infos($stock_infos);	    	
	    }
	    
	    $dbh->commit();
	   	$stock_info_set = undef;
	  };
    
    if($@) {
    	$status->{CODE} = 'FAILED';
    	$status->{MSG} = "$@";
    }
    else {
    	$status->{CODE} = 'OK';
    	$status->{MSG} = "Inserted successfully";
    }
    return ($status, time - $start_time);
}

sub _init_db {
    my $self = shift;
    my $url = $self->{URL};
    my $usr = $self->{USER};
    my $pwd = $self->{PASSWORD}; 
    my $dbh = DBI->connect($url, $usr, $pwd, {RaiseError => 0, PrintError => 0}) || die "DB connection not established: $DBI::errstr\n";    
    $self->{DBH} = $dbh;
}

sub _insert_sys_stats {
    my $self = shift;    
    my $stock_info_set = shift;

    my $dbh = $self->{DBH};
    if (!defined($dbh)) {
            $self->_init_db();
            $dbh = $self->{DBH};
    }
    my $market_info = $stock_info_set->market_info();
    my $processing_time = $stock_info_set->processing_time();
    my $start_time = strftime("%d/%m/%Y %H:%M:%S",localtime($stock_info_set->start_time()));
    my $channel_name = $stock_info_set->from_channel();
    my $trading_date = ($market_info)?$market_info->get('TradingDate'):undef;
    my $trading_time = ($market_info)?$market_info->get('Time'):undef;
    if($trading_date && $trading_time){
    	$trading_date .= " " . $trading_time;
    }else{
    	$trading_date = undef;
    }
    my $sys_status = $stock_info_set->status() ;
    my $message = $stock_info_set->message();    
    my $seq_id = $stock_info_set->sys_sequence();
    
    my $sql = q{
            BEGIN
              pkg_stock.insert_sys_stats (
              			:p_seq_id,
                        :p_channel_id,
                        :p_processing_time,
                         to_date(:p_start_time,'dd/mm/yyyy HH24:MI:SS'),
                         to_date(:p_trading_date,'dd/mm/yyyy HH24:MI:SS'),
                        :p_status,
                        :p_message
                        );
            END;
            };
    my $csr = $dbh->prepare($sql) || die "Can't prepare $sql: $dbh->errstr\n";
    
    $csr->bind_param(":p_seq_id", $seq_id, SQL_DOUBLE);
    $csr->bind_param(":p_channel_id", $channel_name, SQL_VARCHAR);
    $csr->bind_param(":p_processing_time", $processing_time, SQL_INTEGER);
    $csr->bind_param(":p_start_time", $start_time, SQL_VARCHAR);
    $csr->bind_param(":p_trading_date",$trading_date, SQL_VARCHAR);
    $csr->bind_param(":p_status", $sys_status, SQL_VARCHAR);
    $csr->bind_param(":p_message", $message, SQL_VARCHAR);
    
    $csr->execute() || die "can't execute $sql: $dbh->errstr\n";
    $csr->finish();    
    $market_info = undef;
    $stock_info_set = undef
}

sub _insert_stock_infos {
    my $self = shift;
    my $stock_infos = shift;
    
    my $dbh = $self->{DBH};
    if (!defined($dbh)) {
        $self->_init_db();
        $dbh = $self->{DBH};
    }
    my $sql = q{
            BEGIN
              pkg_stock.insert_stock_info (
		        :p_seq_id,
		        :p_channel_id,
				:p_stock_symbol,
				:p_description,
				:p_basic_price,
				:p_ceiling_price,
				:p_floor_price,
				:p_offer_price1,
				:p_offer_volume1,
				:p_bid_price1,
				:p_bid_volume1,
				:p_offer_price2,
				:p_offer_volume2,
				:p_bid_price2,
				:p_bid_volume2,
				:p_offer_price3,
				:p_offer_volume3,
				:p_bid_price3,
				:p_bid_volume3,
				:p_match_price,
				:p_match_volume,
				:p_match_change,
				:p_open_price,
				:p_close_price,
				:p_highest_price,
				:p_lowest_price);
            END;
            };
    my $csr = $dbh->prepare($sql) or die "can't prepare $sql: $dbh->errstr\n";
    foreach my $stock_info (@{$stock_infos}) {    	  	      
        $csr->bind_param(":p_seq_id", $stock_info->get('SYS_SEQ'), SQL_DOUBLE);
        $csr->bind_param(":p_channel_id", $stock_info->get('CHANNEL_NAME'), SQL_VARCHAR);
        $csr->bind_param(":p_stock_symbol", $stock_info->get('Symbol'), SQL_VARCHAR);
        $csr->bind_param(":p_description",$stock_info->get('Description'), SQL_VARCHAR);
        $csr->bind_param(":p_basic_price", $stock_info->get('RefPrice'), SQL_DOUBLE);
        $csr->bind_param(":p_ceiling_price",$stock_info->get('CeilingPrice'), SQL_DOUBLE);
        $csr->bind_param(":p_floor_price", $stock_info->get('FloorPrice'), SQL_DOUBLE);
        $csr->bind_param(":p_offer_price1", $stock_info->get('Best1OffPrice'), SQL_DOUBLE);
        $csr->bind_param(":p_offer_volume1",$stock_info->get('Best1OffVolume'), SQL_DOUBLE);
        $csr->bind_param(":p_bid_price1", $stock_info->get('Best1BidPrice'), SQL_DOUBLE);
        $csr->bind_param(":p_bid_volume1", $stock_info->get('Best1BidVolume'), SQL_DOUBLE);
        $csr->bind_param(":p_offer_price2", $stock_info->get('Best2OffPrice'), SQL_DOUBLE);
        $csr->bind_param(":p_offer_volume2",$stock_info->get('Best2OffVolume'), SQL_DOUBLE);
        $csr->bind_param(":p_bid_price2", $stock_info->get('Best2BidPrice'), SQL_DOUBLE);
        $csr->bind_param(":p_bid_volume2", $stock_info->get('Best2BidVolume'), SQL_DOUBLE);
        $csr->bind_param(":p_offer_price3", $stock_info->get('Best3OffPrice'), SQL_DOUBLE);
        $csr->bind_param(":p_offer_volume3",$stock_info->get('Best3OffVolume'), SQL_DOUBLE);
        $csr->bind_param(":p_bid_price3", $stock_info->get('Best3BidPrice'), SQL_DOUBLE);
        $csr->bind_param(":p_bid_volume3", $stock_info->get('Best3BidVolume'), SQL_DOUBLE);
        $csr->bind_param(":p_match_price", $stock_info->get('TempPrice'), SQL_DOUBLE);
        $csr->bind_param(":p_match_volume",$stock_info->get('TempVolume'), SQL_DOUBLE);
        $csr->bind_param(":p_match_change",$stock_info->get('ChangedPrice'), SQL_VARCHAR);
        $csr->bind_param(":p_open_price",$stock_info->get('SS1MatchPrice'), SQL_DOUBLE);
        $csr->bind_param(":p_close_price",$stock_info->get('ClosePrice'), SQL_DOUBLE);
        $csr->bind_param(":p_highest_price",$stock_info->get('HighestPrice'), SQL_DOUBLE);
        $csr->bind_param(":p_lowest_price",$stock_info->get('LowestPrice'), SQL_DOUBLE);
        $csr->execute() || die "can't execute $sql: " . $dbh->errstr . "\n";        
        $stock_info = undef;
    }
    $csr->finish();
    $stock_infos = undef;
        
}

sub DESTROY{    
	my $self = shift;
	my $dbh = $self->{DBH};
    if (defined($dbh)) {
    	$dbh->disconnect() || die "$DBI::errstr\n";
    }
}
1;


sub memory_usage {
  my $self = shift;
  my $t = new Proc::ProcessTable;
  foreach my $got ( @{$t->table} ) {
    next if not $got->pid eq $$;
    return $got->size;
  }
}
